﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using RTSoft.Modules;


namespace RTSoft.SoureCode.Control.NhatKyNhap
{
    class nhatkynhapctrl
    {
        DataProvider provider = new DataProvider();
        SqlCommand cmd = new SqlCommand();
        String strSQL = "";

        public void getData(ref String errConnect, ref String errSQL, ref DataTable tbl)
        {
            provider.Connection(ref errConnect);
            try
            {
                tbl = new DataTable();
                strSQL = "select * from NHATKYNHAP order by ngaynhap";
                tbl = provider.LayDanhSach(strSQL);
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
            }
            finally
            {
                provider.Disconnect();
            }
        }       

        public void getDataFrom(ref String errConnect, ref String errSQL, ref DataTable tbl, String start , String end)
        {
            provider.Connection(ref errConnect);
            try
            {
                tbl = new DataTable();
                strSQL = "select * from NHATKYNHAP where ngaynhap between Convert(Datetime,'" + start + "',103)  and Convert(Datetime,'" + end + "',103) order by ngaynhap";
                //Console.WriteLine(strSQL);
                tbl = provider.LayDanhSach(strSQL);
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
            }
            finally
            {
                provider.Disconnect();
            }
        }

        public void getDataXuat(ref String errConnect, ref String errSQL, ref DataTable tbl, String start, String end)
        {
            provider.Connection(ref errConnect);
            try
            {
                tbl = new DataTable();
                strSQL = "select * from NHATKYXUAT where ngayxuat between Convert(Datetime,'" + start + "',103)  and Convert(Datetime,'" + end + "',103) order by ngayxuat";
                //Console.WriteLine(strSQL);
                tbl = provider.LayDanhSach(strSQL);
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
            }
            finally
            {
                provider.Disconnect();
            }
        }

        public void getDataXuatReport(ref String errConnect, ref String errSQL, ref DataTable tbl, String start, String end)
        {
            provider.Connection(ref errConnect);
            try
            {
                tbl = new DataTable();
                strSQL = "select * from NHATKYXUAT a, KHACHHANG b where a.id_khachhang = b.ID_KH and ngayxuat between Convert(Datetime,'" + start + "',103)  and Convert(Datetime,'" + end + "',103) order by ngayxuat";
                //Console.WriteLine(strSQL);
                tbl = provider.LayDanhSach(strSQL);
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
            }
            finally
            {
                provider.Disconnect();
            }
        }

       
        public void Insert(ref String errConnect, ref String errSQL, nhatkynhapinfo info)
        {
            cmd.Connection = provider.Connection(ref errConnect);
            if (errConnect != "")
            {
                return;
            }
            cmd.Transaction = provider.BeginTransaction();
            try
            {
                SqlParameter f = new SqlParameter();
                f.IsNullable = true;
                f.Value = System.DBNull.Value;
                strSQL = "Insert into NHATKYNHAP";
                strSQL = strSQL + "(id_mathang,ten_mathang,soluong,ngaynhap,id_nv,ten_nv)";
                strSQL = strSQL + " values";
                strSQL = strSQL + "(@id_mathang,@ten_mathang,@soluong,@ngaynhap,@id_nv,@ten_nv)";
                cmd.CommandText = strSQL;
                Console.WriteLine(strSQL);
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@id_mathang", info.id_mathang);
                cmd.Parameters.AddWithValue("@ten_mathang", info.ten_mathang);
                cmd.Parameters.AddWithValue("@soluong", info.soluong);
                cmd.Parameters.AddWithValue("@ngaynhap", info.ngaynhap);
                cmd.Parameters.AddWithValue("@id_nv", info.id_nv);
                cmd.Parameters.AddWithValue("@ten_nv", info.ten_nv);
                cmd.ExecuteNonQuery();
                provider.CommitTransaction();
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
                provider.RollbackTransaction();
                //' Neu xay ra loi thi rollback lai
            }
            finally
            {
                //'Dong ket noi
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }

        }

        public void Update(ref String errConnect, ref String errSQL, nhatkynhapinfo info)
        {
            cmd.Connection = provider.Connection(ref errConnect);
            if (errConnect != "")
            {
                return;
            }
            cmd.Transaction = provider.BeginTransaction();
            try
            {
                SqlParameter f = new SqlParameter();
                f.IsNullable = true;
                f.Value = System.DBNull.Value;
                strSQL = "Update NHATKYNHAP SET  ";
                strSQL = strSQL + "id_mathang=@id_mathang,ten_mathang=@ten_mathang,soluong=@soluong,ngaynhap=@ngaynhap,id_nv=@id_nv,ten_nv=@ten_nv Where  id_nhap = @id_nhap";
                cmd.CommandText = strSQL;

                cmd.Parameters.AddWithValue("@id_nhap", info.id_nhap);
                cmd.Parameters.AddWithValue("@id_mathang", info.id_mathang);
                cmd.Parameters.AddWithValue("@ten_mathang", info.ten_mathang);
                cmd.Parameters.AddWithValue("@soluong", info.soluong);
                cmd.Parameters.AddWithValue("@ngaynhap", info.ngaynhap);
                cmd.Parameters.AddWithValue("@id_nv", info.id_nv);
                cmd.Parameters.AddWithValue("@ten_nv", info.ten_nv);
                cmd.ExecuteNonQuery();
                provider.CommitTransaction();
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
                provider.RollbackTransaction();
                //' Neu xay ra loi thi rollback lai
            }
            finally
            {
                //'Dong ket noi
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }

        }

        public void Delete(ref String errConnect, ref String errSQL, int id)
        {
            cmd.Connection = provider.Connection(ref errConnect);
            if (errConnect != "")
            {
                return;
            }
            cmd.Transaction = provider.BeginTransaction();
            try
            {
                strSQL = "DELETE FROM NHATKYNHAP WHERE id_nhap=@RowID";
                cmd.Parameters.AddWithValue("@RowID", id);
                cmd.CommandText = strSQL;
                cmd.ExecuteNonQuery();
                provider.CommitTransaction();
                //' Thuc hien transaction 
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
                provider.RollbackTransaction();
                //' Neu xay ra loi thi rollback lai
            }
            finally
            {
                //'Dong ket noi
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }
        }
    }
}
